﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;
using System.Globalization;

namespace sistemaAcademicoFinanceiroEscolas
{
    public partial class relatorioFinanceiroAluno : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                SqlConnection sqlc = new SqlConnection(ConfigurationManager.AppSettings["baseDados"]);
                sqlc.Open();

                string sqlAluno = "select * from aluno order by nomeAluno";

                SqlCommand cmdAluno = new SqlCommand(sqlAluno, sqlc);
                SqlDataReader myReaderAluno = cmdAluno.ExecuteReader();

                ListItem AlunoSelecione = new ListItem();
                AlunoSelecione.Value = "0";
                AlunoSelecione.Text = "Selecione um Aluno";

                lstAluno.Items.Add(AlunoSelecione);

                if (myReaderAluno.HasRows)
                {

                    while (myReaderAluno.Read())
                    {
                        ListItem Aluno = new ListItem();

                        Aluno.Value = myReaderAluno["codigoAluno"].ToString();
                        Aluno.Text = myReaderAluno["nomeAluno"].ToString();

                        lstAluno.Items.Add(Aluno);
                    }

                }
                //Aluno carregados
                myReaderAluno.Close();
                cmdAluno.Dispose();

                // fechando conexão com o banco de dados
                sqlc.Close();

                TxtDataInicial.Focus();
            }

        }


        void relatorio()
        {

            string dataInicial = TxtDataInicial.Text;
            string dataFinal = TxtDataFinal.Text;
            //string codigoAluno = lstAluno.SelectedValue;
            //validarValores vV = new validarValores();

            //Response.Write(dataFinal + "<br>");


            //Response.End();


            TimeSpan dif = Convert.ToDateTime(dataFinal).Subtract(Convert.ToDateTime(dataInicial));



            if (dif.Days >= 0)
            {
                SqlConnection sqlc = new SqlConnection(ConfigurationManager.AppSettings["baseDados"]);
                sqlc.Open();

                //DateTime InicialInsert = new DateTime(Convert.ToInt16(dataInicial.Substring(6, 4)), Convert.ToInt16(dataInicial.Substring(3, 2)), Convert.ToInt16(dataInicial.Substring(0, 2)));
                //string horaInicialBrasil = InicialInsert.ToString("t", CultureInfo.CreateSpecificCulture("pt-BR")); //  10/12/2012

                //DateTime FinalInsert = new DateTime(Convert.ToInt16(dataFinal.Substring(6, 4)), Convert.ToInt16(dataFinal.Substring(3, 2)), Convert.ToInt16(dataFinal.Substring(0, 2)));
                //string horaFinalBrasil = InicialInsert.ToString("t", CultureInfo.CreateSpecificCulture("pt-BR"));                


                string selectAluno = "";

                if (lstAluno.SelectedValue == "0")
                {

                    selectAluno = " SELECT a.codigoAluno, a.horaInicialAula as horaInicialAula, ";
                    selectAluno += " a.horaFinalAula as horaFinalAula, a.codigoProfessor, ";

                    selectAluno += "(SELECT MIN(horaInicialAula) AS horaInicial FROM aula AS aa WHERE (codigoProfessor = a.codigoProfessor) AND (codigoAluno = a.codigoAluno) AND (dataAula = a.dataAula)) AS horaInicialIndividual, ";
                    selectAluno += "(SELECT MAX(horaFinalAula) AS horaFinal FROM aula AS aaa WHERE (codigoProfessor = a.codigoProfessor) AND (codigoAluno = a.codigoAluno) AND (dataAula = a.dataAula)) AS horaFinalIndividual, ";

                    selectAluno += "(SELECT SUM(CAST(valorHora  AS decimal(12, 2))) AS valorTotal FROM aula as aaaa WHERE (codigoProfessor = a.codigoProfessor) AND (codigoAluno = a.codigoAluno) AND (dataAula = a.dataAula)) as valotTotalTotal, ";

                    selectAluno += "(SELECT SUM(CAST(valorPago AS decimal(12, 2))) AS valorTotal FROM aula as aaaa WHERE (codigoProfessor = a.codigoProfessor) AND (codigoAluno = a.codigoAluno) AND (dataAula = a.dataAula)) as valorPagoTotal, ";

                    selectAluno += " a.valorTotal as valorTotal, a.valorPago as valorPago, al.codigoAluno, al.nomeAluno as nomeAluno, al.nomeAluno as nomeAluno ";
                    selectAluno += " FROM aula a , aluno al ";

                    selectAluno += " WHERE ";
                    selectAluno += " a.codigoAluno = al.codigoAluno and ";
                    selectAluno += " convert(datetime,dataAula,103) ";
                    selectAluno += " BETWEEN convert(datetime,'" + dataInicial + "',103) AND convert(datetime,'" + dataFinal + "',103) order by al.nomeAluno ";


                }
                else
                {

                    selectAluno = " SELECT a.codigoAluno, a.horaInicialAula as horaInicialAula, a.horaFinalAula as horaFinalAula, ";
                    selectAluno += " a.valorTotal as valorTotal, a.valorPago as valorPago, a.codigoProfessor, al.codigoAluno, al.nomeAluno as nomeAluno, ";

                    selectAluno += "(SELECT MIN(horaInicialAula) AS horaInicial FROM aula AS aa WHERE (codigoProfessor = a.codigoProfessor) AND (codigoAluno = a.codigoAluno) AND (dataAula = a.dataAula)) AS horaInicialIndividual, ";
                    selectAluno += "(SELECT MAX(horaFinalAula) AS horaFinal FROM aula AS aaa WHERE (codigoProfessor = a.codigoProfessor) AND (codigoAluno = a.codigoAluno) AND (dataAula = a.dataAula)) AS horaFinalIndividual, ";

                    selectAluno += "(SELECT SUM(CAST(valorHora  AS decimal(12, 2))) AS valorTotal FROM aula as aaaa WHERE (codigoProfessor = a.codigoProfessor) AND (codigoAluno = a.codigoAluno) AND (dataAula = a.dataAula)) as valotTotalTotal, ";

                    selectAluno += "(SELECT SUM(CAST(valorPago AS decimal(12, 2))) AS valorTotal FROM aula as aaaa WHERE (codigoProfessor = a.codigoProfessor) AND (codigoAluno = a.codigoAluno) AND (dataAula = a.dataAula)) as valorPagoTotal ";

                    selectAluno += " FROM aula a , aluno al ";

                    selectAluno += " WHERE ";
                    selectAluno += " a.codigoAluno = al.codigoAluno and ";

                    selectAluno += " a.codigoAluno = " + lstAluno.SelectedValue + " and  ";

                    selectAluno += " convert(datetime,dataAula,103) ";

                    selectAluno += " BETWEEN convert(datetime,'" + dataInicial + "',103) AND convert(datetime,'" + dataFinal + "',103) order by al.nomeAluno ";

                }

                SqlCommand cmdAluno = new SqlCommand(selectAluno, sqlc);
                SqlDataReader drAluno = cmdAluno.ExecuteReader();

                //essa string abaixo receberá o código html da tabela
                string tabela = "<table width=\"100%\" border=\"1\" cellpadding=\"0\" cellspacing=\"0\" bordercolor=\"#99CCFF\">";
                tabela += "<tr align=\"center\">";
                tabela += "<td align=\"center\" style=\"font-family:Verdana;font-size:12px;\">";
                tabela += "Aluno";
                tabela += "</td>";
                tabela += "<td align=\"center\" style=\"font-family:Verdana;font-size:12px\">";
                tabela += "Hora Inicial e Hora Final</td>";
                tabela += "</td>";
                tabela += "<td align=\"center\" style=\"font-family:Verdana;font-size:12px\">";
                tabela += "Valor Total</td>";
                tabela += "<td align=\"center\" style=\"font-family:Verdana;font-size:12px\">";
                tabela += "Valor Pago</td>";
                tabela += "<td align=\"center\" style=\"font-family:Verdana;font-size:12px\">";
                tabela += "Status</td>";
                tabela += "</tr>";

                string codigoAluno = "";
                string nomeAluno = "";
                string horaInicialAula = "";
                string horaFinalAula = "";
                string valorTotal = "";
                string valorPago = "";
                //string Status = "";

                string codigoProfessorAluno = "";

                if (drAluno.HasRows)
                {
                    int cont = 1;


                    while (drAluno.Read())
                    {

                        //SE FOR O PROFESSOR E O ALUNO QUE ESTAO CONCATENADOS
                        if (codigoProfessorAluno != drAluno["codigoProfessor"].ToString() + drAluno["codigoAluno"].ToString())
                        {
                            codigoAluno = drAluno["codigoAluno"].ToString();
                            nomeAluno = drAluno["nomeAluno"].ToString();

                            string selectAula = "";


                            horaInicialAula = drAluno["horaInicialIndividual"].ToString();
                            horaFinalAula = drAluno["horaFinalIndividual"].ToString();
                            valorTotal = drAluno["valotTotalTotal"].ToString();
                            valorPago = drAluno["valorPagoTotal"].ToString();

                            //AQUI CONTEUDO DO RELATÓRIO//

                            tabela += "<tr>";
                            tabela += "<td align=\"center\" style=\"font-family:Verdana; font-size:9px\">" + nomeAluno + "</td>";
                            tabela += "<td align=\"center\" style=\"font-family:Verdana; font-size:9px\">" + horaInicialAula + " às " + horaFinalAula + " </td>";
                            tabela += "<td align=\"center\" style=\"font-family:Verdana; font-size:9px\">" + valorTotal + "</td>";
                            tabela += "<td align=\"center\" style=\"font-family:Verdana; font-size:9px\">" + valorPago + "</td>";


                            if (valorPago == "")
                            {
                                tabela += "<td style=\"background-color:#FF0000\"></td>"; //Vermelho

                            }
                            else if (Convert.ToDouble(valorPago) < Convert.ToDouble(valorTotal))
                            {
                                tabela += "<td style=\"background-color:#FFFF00\"></td>"; // Amarelo
                            }
                            else
                            {
                                tabela += "<td style=\"background-color:#00FF00\"></td>"; // verde
                            }

                        }

                        codigoProfessorAluno = drAluno["codigoProfessor"].ToString() + drAluno["codigoAluno"].ToString();

                    }

                }

                drAluno.Close();
                cmdAluno.Dispose();

                tabela += "</table>";
                Panel1.Controls.Add(new LiteralControl(tabela));

                sqlc.Close();

            }

              //fechar conexao
            else
            {
                string literalcc = "<Script language=\"javascript\">function erro() {alert('A data final deve ser maior que a data inicial');}</script><script language=JavaScript>erro();</script>";
                Panel1.Controls.Add(new LiteralControl(literalcc));
            }

        }

        protected void btnGerar_Click(object sender, EventArgs e)
        {
            relatorio();
        }
    }
}

